package com.hui.account.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.AbstractWindowedCursor;
import android.database.Cursor;
import android.database.CursorWindow;
import android.database.sqlite.SQLiteDatabase;
import android.os.Build;
import android.text.TextUtils;
import android.util.Log;

import androidx.annotation.RequiresApi;

import com.hui.account.R;
import com.hui.account.entity.FileEntity;
import com.hui.account.entity.RecordEntity;
import com.hui.account.entity.UserEntity;
import com.hui.account.utils.CommonUtils;
import com.hui.account.utils.TimeUtils;

import java.io.File;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库DML
 */
public class SQLManage {

    private static SQLiteDatabase db;

    public static void initDatabase(Context context) {
        DatabaseConfig helper = new DatabaseConfig(context);
        db = helper.getWritableDatabase();
    }

    /*========================================= User SQL =======================*/

    /**
     * 添加客户
     */
    public static void addUser(UserEntity u) {
        if (null != u) {
            ContentValues values = new ContentValues();
            values.put("userName", u.getUserName());
            values.put("phone", u.getPhone());
            values.put("remark", u.getRemark());
            values.put("status", u.getStatus());
            db.insert("user", null, values);
        }
    }

    /**
     * 修改客户
     */
    public static void modUser(UserEntity u) {
        if (null != u) {
            ContentValues values = new ContentValues();
            values.put("userName", u.getUserName());
            values.put("phone", u.getPhone());
            values.put("remark", u.getRemark());
            db.update("user", values, "id=?", new String[]{String.valueOf(u.getId())});
        }
    }

    /**
     * 删除客户
     */
    public static void delUser(Integer userId) {
        if (null != userId) {
            db.delete("user", "id=?", new String[]{String.valueOf(userId)});
        }
    }

    /**
     * 根据客户id查询客户
     */
    public static UserEntity getUserById(Integer userId) {
        if (null != userId) {
            String sql = "select * from user where id=? order by id desc limit 1";
            Cursor c = db.rawQuery(sql, new String[]{String.valueOf(userId)});
            if (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int userName = c.getColumnIndex("userName");
                int phone = c.getColumnIndex("phone");
                int remark = c.getColumnIndex("remark");
                int status = c.getColumnIndex("status");
                return new UserEntity(c.getInt(id), c.getString(userName), c.getString(phone), c.getString(remark), c.getInt(status));
            }
            c.close();
        }
        return null;
    }

    /**
     * 根据客户姓名查询客户
     */
    public static UserEntity getUserByName(String userName) {
        if (!TextUtils.isEmpty(userName)) {
            String sql = "select * from user where userName=? order by id desc limit 1";
            Cursor c = db.rawQuery(sql, new String[]{String.valueOf(userName)});
            if (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int phone = c.getColumnIndex("phone");
                int remark = c.getColumnIndex("remark");
                int status = c.getColumnIndex("status");
                return new UserEntity(c.getInt(id), userName, c.getString(phone), c.getString(remark), c.getInt(status));
            }
            c.close();
        }
        return null;
    }

    /**
     * 根据客户姓名模糊查询客户
     */
    public static List<UserEntity> getUserByNameVague(String value) {
        List<UserEntity> list = new ArrayList<>();
        if (!TextUtils.isEmpty(value)) {
            String sql = "select * from user where status=0 and userName like '%" + value + "%' order by id desc";
            Cursor c = db.rawQuery(sql, null);
            while (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int userName = c.getColumnIndex("userName");
                int phone = c.getColumnIndex("phone");
                int remark = c.getColumnIndex("remark");
                int status = c.getColumnIndex("status");
                list.add(new UserEntity(c.getInt(id), c.getString(userName), c.getString(phone), c.getString(remark), c.getInt(status)));
            }
            c.close();
        }
        return list;
    }

    /**
     * 分页查询全部客户
     */
    public static List<UserEntity> getUsers(Integer status, int curPage, int pageSize) {
        List<UserEntity> list = new ArrayList<>();

        String sql = null;
        Cursor c = null;
        if (null == status) {
            sql = "select * from user limit ?,?";
            c = db.rawQuery(sql, new String[]{String.valueOf(curPage), String.valueOf(pageSize)});
        } else {
            sql = "select * from user where status=? order by id desc limit ?,?";
            c = db.rawQuery(sql, new String[]{String.valueOf(status), String.valueOf(curPage), String.valueOf(pageSize)});
        }

        while (c.moveToNext()) {
            int id = c.getColumnIndex("id");
            int userName = c.getColumnIndex("userName");
            int phone = c.getColumnIndex("phone");
            int remark = c.getColumnIndex("remark");
            status = c.getColumnIndex("status");
            list.add(new UserEntity(c.getInt(id), c.getString(userName), c.getString(phone), c.getString(remark), c.getInt(status)));
        }
        c.close();
        return list;
    }

    /*========================================= Record SQL=======================*/

    /**
     * 添加欠款记录
     */
    public static void addRecord(RecordEntity r) {
        if (null != r) {
            ContentValues values = new ContentValues();
            values.put("icon", r.getIcon());
            values.put("userId", r.getUserId());
            values.put("type", r.getType());
            values.put("status", r.getStatus());
            values.put("imageName", r.getImageName());
            values.put("pay", r.getPay());
            values.put("remark", r.getRemark());
            values.put("money", String.valueOf(r.getMoney()));
            values.put("time", r.getTime());
            values.put("year", r.getYear());
            values.put("month", r.getMonth());
            values.put("day", r.getDay());
            values.put("payTime", String.valueOf(r.getPayTime()));
            values.put("createTime", String.valueOf(r.getCreateTime()));
            db.insert("record", null, values);
        }
    }

    /**
     * 查询本月欠款记录
     */
    public static List<RecordEntity> getMonthRecords(Integer year, Integer month) {
        List<RecordEntity> list = new ArrayList<>();
        if (null != year && null != month) {
            String sql = "select * from record where status=0 and year=? and month=? order by createTime desc";
            Cursor c = db.rawQuery(sql, new String[]{String.valueOf(year), String.valueOf(month)});
            while (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int icon = c.getColumnIndex("icon");
                int userId = c.getColumnIndex("userId");
                int type = c.getColumnIndex("type");
                int status = c.getColumnIndex("status");
                int imageName = c.getColumnIndex("imageName");
                int pay = c.getColumnIndex("pay");
                int remark = c.getColumnIndex("remark");
                int money = c.getColumnIndex("money");
                int time = c.getColumnIndex("time");
                int day = c.getColumnIndex("day");
                int payTime = c.getColumnIndex("payTime");
                int createTime = c.getColumnIndex("createTime");
                RecordEntity entity = null;
                try {
                    entity = new RecordEntity(c.getInt(id), c.getInt(icon), c.getInt(userId), c.getInt(type), c.getInt(status), c.getString(imageName), c.getString(pay), c.getString(remark), new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP), c.getString(time), year, month, c.getInt(day), TimeUtils.getTimestampToString(c.getString(payTime)), TimeUtils.getTimestampToString(c.getString(createTime)));
                } catch (Exception e) {
                    Log.e(CommonUtils.TAG, "getMonthRecords 参数解析错误！ 记录id=" + c.getInt(id) + " 金额：" + c.getString(money) + " 还款日期：" + c.getString(payTime) + " 创建时间：" + c.getString(createTime));
                    continue;
                }
                list.add(entity);
            }
            c.close();
        }
        return list;
    }

    /**
     * 查询用户全部账单
     */
    public static List<RecordEntity> getUserRecords(Integer type, Integer status, Integer userId) {
        List<RecordEntity> list = new ArrayList<>();

        String sql = null;
        Cursor c = null;
        if (null == status) {
            sql = "select * from record where type=? and userId=? order by createTime desc";
            c = db.rawQuery(sql, new String[]{String.valueOf(type), String.valueOf(userId)});
        } else {
            sql = "select * from record where type=? and status=? and userId=? order by createTime desc";
            c = db.rawQuery(sql, new String[]{String.valueOf(type), String.valueOf(status), String.valueOf(userId)});
        }

        while (c.moveToNext()) {
            int id = c.getColumnIndex("id");
            int icon = c.getColumnIndex("icon");
            type = c.getColumnIndex("type");
            status = c.getColumnIndex("status");
            int imageName = c.getColumnIndex("imageName");
            int pay = c.getColumnIndex("pay");
            int remark = c.getColumnIndex("remark");
            int money = c.getColumnIndex("money");
            int time = c.getColumnIndex("time");
            int year = c.getColumnIndex("year");
            int month = c.getColumnIndex("month");
            int day = c.getColumnIndex("day");
            int payTime = c.getColumnIndex("payTime");
            int createTime = c.getColumnIndex("createTime");
            RecordEntity entity = null;
            try {
                entity = new RecordEntity(c.getInt(id), c.getInt(icon), userId, c.getInt(type), c.getInt(status), c.getString(imageName), c.getString(pay), c.getString(remark), new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP), c.getString(time), c.getInt(year), c.getInt(month), c.getInt(day), TimeUtils.getTimestampToString(c.getString(payTime)), TimeUtils.getTimestampToString(c.getString(createTime)));
            } catch (Exception e) {
                Log.e(CommonUtils.TAG, "getUserRecords 参数解析错误！ 记录id=" + c.getInt(id) + " 金额：" + c.getString(money) + " 还款日期：" + c.getString(payTime) + " 创建时间：" + c.getString(createTime));
                continue;
            }
            list.add(entity);
        }
        c.close();
        return list;
    }

    /**
     * 查询用户全部未还欠款单
     */
    public static List<RecordEntity> getUserRecords(List<Integer> userIds) {
        List<RecordEntity> list = new ArrayList<>();
        if (userIds.size() > 0) {
            String[] str = new String[userIds.size()];
            StringBuilder sb = new StringBuilder("type=0 and status=0 and userId in (");
            for (int i = 0; i < userIds.size(); i++) {
                str[i] = String.valueOf(userIds.get(i));
                if (i != userIds.size() - 1) {
                    sb.append("?,");
                } else {
                    sb.append("?");
                }
            }
            sb.append(")");

            Cursor c = db.query("record", null, sb.toString(), str, null, null, "createTime desc");
            while (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int icon = c.getColumnIndex("icon");
                int userId = c.getColumnIndex("userId");
                int type = c.getColumnIndex("type");
                int status = c.getColumnIndex("status");
                int imageName = c.getColumnIndex("imageName");
                int pay = c.getColumnIndex("pay");
                int remark = c.getColumnIndex("remark");
                int money = c.getColumnIndex("money");
                int time = c.getColumnIndex("time");
                int year = c.getColumnIndex("year");
                int month = c.getColumnIndex("month");
                int day = c.getColumnIndex("day");
                int payTime = c.getColumnIndex("payTime");
                int createTime = c.getColumnIndex("createTime");
                RecordEntity entity = null;
                try {
                    entity = new RecordEntity(c.getInt(id), c.getInt(icon), c.getInt(userId), c.getInt(type), c.getInt(status), c.getString(imageName), c.getString(pay), c.getString(remark), new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP), c.getString(time), c.getInt(year), c.getInt(month), c.getInt(day), TimeUtils.getTimestampToString(c.getString(payTime)), TimeUtils.getTimestampToString(c.getString(createTime)));
                } catch (Exception e) {
                    Log.e(CommonUtils.TAG, "getUserRecords 参数解析错误！ 记录id=" + c.getInt(id) + " 金额：" + c.getString(money) + " 还款日期：" + c.getString(payTime) + " 创建时间：" + c.getString(createTime));
                    continue;
                }
                list.add(entity);
            }
            c.close();
        }
        return list;
    }

    /**
     * 查询执行逻辑删除的欠款记录
     */
    public static List<RecordEntity> getLogicDelRecords() {
        List<RecordEntity> list = new ArrayList<>();
        String sql = "select * from record where status=1 order by id desc";
        Cursor c = db.rawQuery(sql, null);
        while (c.moveToNext()) {
            int id = c.getColumnIndex("id");
            int icon = c.getColumnIndex("icon");
            int userId = c.getColumnIndex("userId");
            int type = c.getColumnIndex("type");
            int status = c.getColumnIndex("status");
            int imageName = c.getColumnIndex("imageName");
            int pay = c.getColumnIndex("pay");
            int remark = c.getColumnIndex("remark");
            int money = c.getColumnIndex("money");
            int time = c.getColumnIndex("time");
            int year = c.getColumnIndex("year");
            int month = c.getColumnIndex("month");
            int day = c.getColumnIndex("day");
            int payTime = c.getColumnIndex("payTime");
            int createTime = c.getColumnIndex("createTime");
            RecordEntity entity = null;
            try {
                entity = new RecordEntity(c.getInt(id), c.getInt(icon), c.getInt(userId), c.getInt(type), c.getInt(status), c.getString(imageName), c.getString(pay), c.getString(remark), new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP), c.getString(time), c.getInt(year), c.getInt(month), c.getInt(day), TimeUtils.getTimestampToString(c.getString(payTime)), TimeUtils.getTimestampToString(c.getString(createTime)));
            } catch (Exception e) {
                Log.e(CommonUtils.TAG, "getUserRecords 参数解析错误！ 记录id=" + c.getInt(id) + " 金额：" + c.getString(money) + " 还款日期：" + c.getString(payTime) + " 创建时间：" + c.getString(createTime));
                continue;
            }
            list.add(entity);
        }
        c.close();
        return list;
    }

    /**
     * 查询欠账总人数
     */
    public static int getRecordUserCount() {
        String sql = "select distinct userId from record where type=0 and status=0";
        Cursor c = db.rawQuery(sql, null);
        return c.getCount();
    }

    /**
     * 查询全部欠款记录
     */
    public static List<BigDecimal> getRecordTotalMoney(int curPage, int pageSize) {
        List<BigDecimal> list = new ArrayList<>();
        String sql = "select money from record where type=0 and status=0 limit ?,?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(curPage), String.valueOf(pageSize)});
        while (c.moveToNext()) {
            int money = c.getColumnIndex("money");
            list.add(new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP));
        }
        c.close();
        return list;
    }

    /**
     * 删除欠款单
     */
    public static void delRecord(Integer recordId) {
        if (null != recordId) {
            db.delete("record", "id=?", new String[]{String.valueOf(recordId)});
        }
    }

    /**
     * 还原欠款单
     */
    public static void recoverRecord(Integer recordId) {
        if (null != recordId) {
            ContentValues values = new ContentValues();
            values.put("status", 0);
            db.update("record", values, "id=?", new String[]{String.valueOf(recordId)});
        }
    }

    /**
     * 逻辑删除欠款单
     */
    public static void delLogicRecord(Integer recordId) {
        if (null != recordId) {
            ContentValues values = new ContentValues();
            values.put("status", 1);
            db.update("record", values, "id=?", new String[]{String.valueOf(recordId)});
        }
    }

    /**
     * 删除客户全部账单
     */
    public static void deleteUserRecords(Integer userId) {
        if (null != userId) {
            db.delete("record", "userId=?", new String[]{String.valueOf(userId)});
        }
    }

    /**
     * 清空回收站
     */
    public static void delRecords() {
        db.delete("record", "status=1", null);
    }

    /**
     * 还款
     */
    public static void refundRecord(RecordEntity r) {
        if (null != r) {
            ContentValues values = new ContentValues();
            values.put("icon", r.getIcon());
            values.put("type", r.getType());
            values.put("pay", r.getPay());
            values.put("payTime", String.valueOf(r.getPayTime()));
            db.update("record", values, "id=?", new String[]{String.valueOf(r.getId())});
        }
    }

    /**
     * 修改欠款单
     */
    public static void modRecord(RecordEntity r) {
        if (null != r) {
            ContentValues values = new ContentValues();
            values.put("remark", r.getRemark());
            values.put("imageName", r.getImageName());
            db.update("record", values, "id=?", new String[]{String.valueOf(r.getId())});
        }
    }

    /**
     * 分页查询全部欠款记录
     */
    public static List<RecordEntity> getRecords(int curPage, int pageSize) {
        List<RecordEntity> list = new ArrayList<>();
        String sql = "select * from record limit ?,?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(curPage), String.valueOf(pageSize)});
        while (c.moveToNext()) {
            int id = c.getColumnIndex("id");
            int icon = c.getColumnIndex("icon");
            int userId = c.getColumnIndex("userId");
            int type = c.getColumnIndex("type");
            int status = c.getColumnIndex("status");
            int imageName = c.getColumnIndex("imageName");
            int pay = c.getColumnIndex("pay");
            int remark = c.getColumnIndex("remark");
            int money = c.getColumnIndex("money");
            int time = c.getColumnIndex("time");
            int year = c.getColumnIndex("year");
            int month = c.getColumnIndex("month");
            int day = c.getColumnIndex("day");
            int payTime = c.getColumnIndex("payTime");
            int createTime = c.getColumnIndex("createTime");
            RecordEntity entity = null;
            try {
                entity = new RecordEntity(c.getInt(id), c.getInt(icon), c.getInt(userId), c.getInt(type), c.getInt(status), c.getString(imageName), c.getString(pay), c.getString(remark), new BigDecimal(c.getString(money)).setScale(2, BigDecimal.ROUND_HALF_UP), c.getString(time), c.getInt(year), c.getInt(month), c.getInt(day), TimeUtils.getTimestampToString(c.getString(payTime)), TimeUtils.getTimestampToString(c.getString(createTime)));
            } catch (Exception e) {
                Log.e(CommonUtils.TAG, "getMonthRecords 参数解析错误！ 记录id=" + c.getInt(id) + " 金额：" + c.getString(money) + " 还款日期：" + c.getString(payTime) + " 创建时间：" + c.getString(createTime));
                continue;
            }
            list.add(entity);
        }
        c.close();
        return list;
    }

    /*========================================= File SQL=======================*/

    /**
     * 添加文件
     */
    public static void addFile(FileEntity f) {
        if (null != f) {
            ContentValues values = new ContentValues();
            values.put("name", f.getName());
            values.put("path", f.getPath());
            db.insert("file", null, values);
        }
    }

    /**
     * 删除文件
     */
    public static void delFile(Integer fileId) {
        if (null != fileId) {
            db.delete("file", "id=?", new String[]{String.valueOf(fileId)});
        }
    }

    /**
     * 根据文件名称查询文件
     */
    public static FileEntity getFileByName(String filename) {
        if (!TextUtils.isEmpty(filename)) {
            String sql = "select id,name,path from file where name=? order by id desc limit 1";
            Cursor c = db.rawQuery(sql, new String[]{String.valueOf(filename)});
            if (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int name = c.getColumnIndex("name");
                int path = c.getColumnIndex("path");
                return new FileEntity(c.getInt(id), c.getString(name), c.getString(path));
            }
            c.close();
        }
        return null;
    }

    /**
     * 根据文件名称查询文件
     */
    public static List<FileEntity> getFilesByName(List<String> images) {
        List<FileEntity> list = new ArrayList<>();
        if (images.size() > 0) {
            String[] str = new String[images.size()];
            StringBuilder sb = new StringBuilder("name in (");
            for (int i = 0; i < images.size(); i++) {
                str[i] = String.valueOf(images.get(i));
                if (i != images.size() - 1) {
                    sb.append("?,");
                } else {
                    sb.append("?");
                }
            }
            sb.append(")");
            Cursor c = db.query("file", null, sb.toString(), str, null, null, null);
            while (c.moveToNext()) {
                int id = c.getColumnIndex("id");
                int name = c.getColumnIndex("name");
                int path = c.getColumnIndex("path");
                list.add(new FileEntity(c.getInt(id), c.getString(name), c.getString(path)));
            }
            c.close();
        }
        return list;
    }

    /**
     * 分页查询全部文件
     */
    public static List<FileEntity> getFiles(int curPage, int pageSize) {
        List<FileEntity> list = new ArrayList<>();
        String sql = "select * from file limit ?,?";
        Cursor c = db.rawQuery(sql, new String[]{String.valueOf(curPage), String.valueOf(pageSize)});
        while (c.moveToNext()) {
            int id = c.getColumnIndex("id");
            int name = c.getColumnIndex("name");
            int path = c.getColumnIndex("path");
            list.add(new FileEntity(c.getInt(id), c.getString(name), c.getString(path)));
        }
        c.close();
        return list;
    }

    /**
     * 清空数据库并且重新设置主键
     */
    public static void clearDataBase() {
        db.delete("user", null, null);
        db.delete("file", null, null);
        db.delete("record", null, null);
        db.delete("sqlite_sequence", "name=?", new String[]{"user"});
        db.delete("sqlite_sequence", "name=?", new String[]{"file"});
        db.delete("sqlite_sequence", "name=?", new String[]{"record"});
    }
}
